package com.quanquan.store.repository;

import com.quanquan.store.entity.Coupon;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * @ClassName:CouponRepository
 * @Description:TODO
 * @Author:xiongjunbao
 * @Date:2019/3/18 14:42
 * @Version 1.0
 */
public interface CouponRepository extends JpaRepository<Coupon, String>, JpaSpecificationExecutor<Coupon> {


    @Query(value = "SELECT * FROM oo_coupon WHERE status = 1 and status_show = 0  order by remain_total desc limit ?1,?2", nativeQuery = true)
    List<Coupon>  queryList(Integer count, Integer size);

    @Query(value = "SELECT * FROM oo_coupon WHERE status_hot = ?1 AND status = 1 and status_show = 0 order by remain_total desc limit ?2,?3", nativeQuery = true)
    List<Coupon> queryListByStastusHot(Integer stastusHot, Integer count, Integer size);

    /**
     * 查找id对应优惠券
     *
     * @return
     */
    @Query(value = "SELECT * FROM oo_coupon WHERE id = ?1 ", nativeQuery = true)
    Coupon findByCouponId(String id);

    /**
     * 查找id对应优惠券
     *
     * @return
     */
    @Query(value = "SELECT * FROM oo_coupon WHERE id = ?1", nativeQuery = true)
    Coupon findByCouponIdDetail(String id);

    /**
     * 查找id对应优惠券
     *
     * @return
     */
    @Query(value = "SELECT * FROM oo_coupon WHERE id = ?1 and status = 1 and status_show = 0", nativeQuery = true)
    Coupon findByIdAndStatusShow(String id);

    /**
     * 查找id对应优惠券
     *
     * @return
     */
    @Query(value = "SELECT * FROM oo_coupon WHERE id = ?1", nativeQuery = true)
    Coupon findByCouponIdAndStatus(String id);

    /**
     * 模糊查询商品名称
     *
     * @return
     */
    @Query(value = "SELECT * FROM oo_coupon WHERE LOCATE(?1,name)>0  AND status = 1 and status_show = 0 order by sort_order asc limit ?2,?3", nativeQuery = true)
    List<Coupon> queryListByCouponName(String couponName, Integer count, Integer size);

    /**
     * 根据优惠券类型查询列表
     *
     * @return
     */
    @Query(value = "SELECT * FROM oo_coupon WHERE type =?1 AND status = 1 and status_show = 0 order by remain_total desc limit ?2,?3", nativeQuery = true)
    List<Coupon> queryListByType(Integer type, Integer count, Integer size);

    /**
     * 根据优惠券支付类型查询列表
     *
     * @return
     */
    @Query(value = "SELECT * FROM oo_coupon WHERE value_type =?1 AND status = 1 and status_show = 0 order by remain_total desc limit ?2,?3 ", nativeQuery = true)
    List<Coupon> queryListByValueType(Integer valueType, Integer count, Integer size);

    /**
     * 根据优惠券支付类型查询列表
     *
     * @return
     */
    @Query(value = "SELECT * FROM oo_coupon WHERE value_type =?1 and type = ?4  AND status = 1 and status_show = 0 order by remain_total desc limit ?2,?3 ", nativeQuery = true)
    List<Coupon> queryListByValueTypeAndType(Integer valueType, Integer count, Integer size, Integer type);


    /**
     * 生成订单时，先将券数扣除
     *
     * @param couponId
     * @return
     */
    @Transactional
    @Modifying
    @Query(value = "update oo_coupon set remain_total = remain_total - ?2 where id = ?1 and remain_total >=?2", nativeQuery = true)
    public int updateRemainTotal(String couponId, Integer count);


    /**
     * 后台用
     *
     * @param type
     * @return
     */
    @Query(value = "SELECT * FROM oo_coupon WHERE STATUS = 0 AND TYPE = ?1", nativeQuery = true)
    List<Coupon> findAlls(Integer type);


    /**
     * 判断优惠券是否上架
     * @param id
     * @param
     * @return
     */
    @Query(value = "SELECT * FROM oo_coupon WHERE id = ?1 ", nativeQuery = true)
    Coupon findByIdAndStatus(String id);


    /**
     * 后台用

     * @param type
     * @return
     */
    @Query(value = "SELECT * FROM oo_coupon WHERE TYPE = ?1 and status = 0", nativeQuery = true)
    List<Coupon> findAllss(Integer type);


    /**
     * 秒杀专用
     * @param couponId
     * @return
     */
    @Query(value = "SELECT * FROM oo_coupon where id = ?1", nativeQuery = true)
    Coupon findKillAll(String couponId);
}
